from openpyxl import Workbook
from openpyxl import load_workbook  # 导入模块
import datetime


class ExcelOp(object):
    def __init__(self, file):
        self.file = file
        self.wb = load_workbook(self.file)
        sheets = self.wb.get_sheet_names()
        self.sheet = sheets[0]
        self.ws = self.wb[self.sheet]

    # 获取表格的总行数和总列数
    def get_row_clo_num(self):
        rows = self.ws.max_row
        columns = self.ws.max_column
        return rows, columns

    # 获取某个单元格的值
    def get_cell_value(self, row, column):
        cell_value = self.ws.cell(row=row, column=column).value
        return cell_value

    # 获取某列的所有值
    def get_col_value(self, column):
        rows = self.ws.max_row
        column_data = []
        for i in range(1, rows + 1):
            cell_value = self.ws.cell(row=i, column=column).value
            column_data.append(cell_value)
        return column_data

    # 获取某行所有值
    def get_row_value(self, row):
        columns = self.ws.max_column
        row_data = []
        for i in range(1, columns + 1):
            cell_value = self.ws.cell(row=row, column=i).value
            row_data.append(cell_value)
        return row_data

    # 设置某个单元格的值
    def set_cell_value(self, row, colunm, cellvalue):
        try:
            self.ws.cell(row=row, column=colunm).value = cellvalue
            self.wb.save(self.file)
        except:
            self.ws.cell(row=row, column=colunm).value = "writefail"

            self.wb.save(self.file)


filepath = r'F:\python\file_tools\生产计划生成ics日历文件\data\3月1-7号周计划.xlsx'
ex = ExcelOp(filepath)
# print(ex.get_row_clo_num())


def get_firstrow_value(ex,firstnumber):
    temp1 =[]
    temp2 =[]
    icount = ex.get_row_clo_num()
    for i in range(firstnumber,icount[0]+1):
        if ex.get_cell_value(i,1)!=None:
            temp1 = []
            temp1.append(i)
            temp1.append(ex.get_cell_value(i, 1))
            temp2.append(temp1)
    temp1 = []
    temp1.append(icount[0]+1)
    temp1.append(ex.get_cell_value(icount[0], 1))
    temp2.append(temp1)
    return temp2
print(get_firstrow_value(ex,4))


def set_ex_time(ex, firstrow, firstnumber):
    icount = ex.get_row_clo_num()
    k = firstrow[0][0]
    
    for i in range(firstnumber, icount[0]+1):
        if str(ex.get_cell_value(i, 2))!= '':
            try:
                temptime = str(ex.get_cell_value(i, 2)).split('-')
                temp1 = int(temptime[0].split(':')[0])
                temp2 = int(temptime[0].split(':')[1])
                temp = datetime.time(temp1,temp2,0) 
                ex.set_cell_value(i, 9, temp)
            except Exception as e:
                print(e)
                
            if '日' in str(ex.get_cell_value(i, 2)):
                if '月' in str(ex.get_cell_value(i, 2)):
                    try:
                        temptime = str(ex.get_cell_value(i, 2)).split('-')
                        k = temptime[1].find('月')
                        k1 = temptime[1].find('日')
                        tp = temptime[1][k1+1:]                        
                        temp1 = int(tp.split(':')[0])
                        temp2 = int(tp.split(':')[1])
                        temp = datetime.time(temp1, temp2, 0)
                        ex.set_cell_value(i, 11, temp)                        
                        tp1 = int(temptime[1][0:k])
                        tp2 = int(temptime[1][k+1:k1])
                        for j in range(0, len(firstrow)):
                            if i >= firstrow[j][0] and i < firstrow[j+1][0]:
                                t = datetime.datetime(
                                    firstrow[j][1].year, tp1, tp2)
                                ex.set_cell_value(i, 10, t)
                    except Exception as e:
                        print(e)
                else:
                    try:
                        temptime = str(ex.get_cell_value(i, 2)).split('-')
                        k = temptime[1].find('日')
                        tp = temptime[1][k+1:]
                        temp1 = int(tp.split(':')[0])
                        temp2 = int(tp.split(':')[1])
                        temp = datetime.time(temp1, temp2, 0)
                        ex.set_cell_value(i, 11, temp)
                        tp1 = int(temptime[1][0:k])
                        for j in range(0, len(firstrow)):
                            if i >= firstrow[j][0] and i < firstrow[j+1][0]:
                                t = datetime.datetime(
                                    firstrow[j][1].year, firstrow[j][1].month, tp1)
                                ex.set_cell_value(i, 10, t)
                    except Exception as e:
                        print(e)
                    
                    
            else:
                try:
                    temptime = str(ex.get_cell_value(i, 2)).split('-')
                    temp1 = int(temptime[1].split(':')[0])
                    temp2 = int(temptime[1].split(':')[1])
                    temp = datetime.time(temp1, temp2, 0)
                    ex.set_cell_value(i, 11, temp)
                except Exception as e:
                    print(e)
                for j in range(0, len(firstrow)):
                    if i >= firstrow[j][0] and i < firstrow[j+1][0]:
                        ex.set_cell_value(i, 10, firstrow[j][1])
            for j in range(0,len(firstrow)):
                if i >= firstrow[j][0] and i < firstrow[j+1][0]:
                    ex.set_cell_value(i, 8, firstrow[j][1])
    


set_ex_time(ex, get_firstrow_value(ex, 4), 4)
